<?php 
$req_path="";
require_once($req_path."include/db.php");

class M_report extends DB{
	private $dbConn;
	private $rowCount="0";
	private $ID="0";
	
	function M_report(){
		parent::__construct();
	}
	function getDataCBO($t){
		if ($t=="y")
			$sqlscript= "select DATE_FORMAT(SaleDate, '%Y') ID, DATE_FORMAT(SaleDate, '%Y') Name  from tb_pos 
						Group by  DATE_FORMAT(SaleDate, '%Y') ";
		
		$result = $this->ExecuteData($sqlscript);
		
		 while($row = $result->FetchRow()){
			$ar[$row["ID"]] = $row["Name"];
		 }
		 
		return $ar;
	}
	//******************************
	// Customer Report
	//******************************
	function CustomerReport($bdateFrom , $bdateTo, $ageFrom , $ageTo, $orderBy){
		
		$sql="select Code, FirstName, LastName,  Case when  Sex='1' Then 'Male' Else 'Female' End Sex 
			, Case when DATE_FORMAT( BirthDay , '%d/%m/%Y') ='00/00/0000' then '' else DATE_FORMAT( BirthDay , '%d/%m/%Y') end  _BirthDay 
			, COALESCE(sum(p.Amount),0) Amount
			, max(FLOOR(DATEDIFF(Now(),BirthDay)/365)) as Age , count(*) as times"
			." from tb_customer c"
			." left join tb_pos p on p.CustID=c.ID where 1=1 ";
		
		if (isset($bdateFrom) && $bdateFrom!="")
			$sql.=" and c.BirthDay between '".$bdateFrom ."' and '".$bdateTo."'";
		
		if (isset($ageFrom) && $ageFrom!="")
			$sql .=" and FLOOR(DATEDIFF(Now(),BirthDay)/365) between ". $ageFrom ." and ".$ageTo;
			
		$sql.=" where p.IsCancel = 0    Group by Code, FirstName, LastName,_BirthDay, Sex ";			
		if ($orderBy=="1")
			$sql .=" order by sum(p.Amount) desc ";
		else 
			$sql .=" order by count(*) desc ";
			
//print $sql;
		$result = $this->ExecuteData($sql);
		$resultData = array();
		while($row = $result->FetchRow()){
			array_push($resultData,$row);
		}
		
		return $resultData;
	}
	function CustomerHeader(){
		return array('ลำดับ','รหัส','ชื่อลูกค้า','วันเกิด','อายุ','เพศ' ,'ยอดใช้จ่าย', 'จำนวนเข้าใช้บริการ');
	}
	
	//******************************
	// Top Sale Report
	//******************************	
	function TopSaleHeader($type=0){
		if ($type==0)
			$ar= array('ลำดับที่','ชื่อสาขา','รายได้รวม','%จากยอดรวม');
		else if ($type==1)
			$ar= array('ลำดับที่','ชื่อสินค้า','จำนวน','ราคา' ,'รายได้รวม','%จากยอดรวม');
		else if ($type==2)
			$ar= array('ลำดับที่','ชื่อ Treatment','จำนวน','ราคา' ,'รายได้รวม','%จากยอดรวม');
		else if ($type==3)
			$ar= array('ลำดับที่','ชื่อ Set','จำนวน','ราคา' ,'รายได้รวม','%จากยอดรวม');
		else if ($type==4)
			$ar= array('ลำดับที่','ชื่อ Course','จำนวน','ราคา' ,'รายได้รวม','%จากยอดรวม');	
			
		return $ar;
	}
	function TopSaleReport($dtFrom , $dtTo, $branch=0, $type=0){
		
		if ($type=="0"){
			$sql="select COALESCE(sum(p.Amount) ,1 ) as TotalAmount "
			." from tb_pos p "
			." join tb_branch b on b.ID=p.Branch"
			." where p.IsCancel=0 and DATE_FORMAT(p.SaleDate,'%Y/%m/%d') between '".$dtFrom."' and '".$dtTo."'";
				
			if (isset($branch) && ($branch)!=0){
				$sql  .= " and p.Branch=".$branch ;
			}
			//get total
			$reAmt = $this->ExecuteData($sql);
			if ($reAmt->RecordCount()>0)
				$TotalAmt=$reAmt->FetchRow();		
			else 
				$TotalAmt['TotalAmount']=1;

			
			$sql="select b.BranchName  as Name, sum(p.Amount) as Amount , (sum(p.Amount) * 100)/".$TotalAmt['TotalAmount']." as Percent"
			." from tb_pos p "
			." join tb_branch b on b.ID=p.Branch"
			." where p.IsCancel=0 and DATE_FORMAT(p.SaleDate,'%Y/%m/%d') between '".$dtFrom."' and '".$dtTo."'";
		
			if (isset($branch) && ($branch)!=0){
				$sql  .= " and p.Branch=".$branch ;
			}
			$sql.=" group by b.BranchName ";
			$sql.="  Order by (sum(p.Amount) * 100)/".$TotalAmt['TotalAmount']." desc ";

			$result = $this->ExecuteData($sql);
			$resultData = array();
			while($row = $result->FetchRow()){
				array_push($resultData,$row);
			}
		
			return $resultData;
		}else {
			$sql="select COALESCE(sum(pd.Amount * pd.Price),1) as TotalAmount "
			." from tb_pos p "
			." join tb_pos_detail pd on pd.Pos_id=p.ID "
			." join tb_branch b on b.ID=p.Branch "
			." join tb_product po on po.ID=pd.Product "
			." where p.IsCancel=0 and po.Type=".$type." and  DATE_FORMAT(p.SaleDate,'%Y/%m/%d') between '".$dtFrom."' and '".$dtTo."'";	
		
			if (isset($branch) && ($branch)!=0){
				$sql  .= " and p.Branch=".$branch ;
			}
			//print $sql. '<br>';
			//get total
			$reAmt = $this->ExecuteData($sql);
			if ($reAmt->RecordCount()>0)
				$TotalAmt=$reAmt->FetchRow();		
			else 
				$TotalAmt['TotalAmount']=1;
			
			$sql="select po.ProductName as Name , sum(pd.Amount) NoSale , max(pd.Price) Price, sum(pd.Amount * pd.Price) as Amount , (sum(pd.Amount * pd.Price) * 100)/".$TotalAmt['TotalAmount']." as Percent"
			." from tb_pos p "
			." join tb_pos_detail pd on pd.Pos_id=p.ID "
			." join tb_branch b on b.ID=p.Branch "
			." join tb_product po on po.ID=pd.Product "
			." where p.IsCancel=0 and po.Type=".$type." and  DATE_FORMAT(p.SaleDate,'%Y/%m/%d') between '".$dtFrom."' and '".$dtTo."'";	
		//print $sql;
			if (isset($branch) && ($branch)!=0){
				$sql  .= " and p.Branch=".$branch ;
			}
			$sql.=" group by po.ProductName ";
			$sql.=" order by (sum(pd.Amount * pd.Price) * 100)/".$TotalAmt['TotalAmount']." desc ";
			//print $sql;
			$result = $this->ExecuteData($sql);
			$resultData = array();
			while($row = $result->FetchRow()){
				array_push($resultData,$row);
			}
		
			return $resultData;			
		}
		
		
	}
	
	//******************************
	// Daily Sale Report
	//******************************		
	function CourseDiff_Header(){
		return array('ชื่อลูกค้า','คอร์สที่ซื้อ','สาขาที่ซื้อ','ราคา','จำนวน' , 'ใช้ไป','จำนวนเงิน');
	}
	function CourseDiff_Detail($datefrom,$dateto, $branch=0){

		$sql="SELECT bs.ID,bs.BranchName as BranchService , p.ProductName,cus.FirstName, cus.LastName "
		." , b.BranchName , c.Prices , c.Times , count(*) as c,  (c.Prices/c.Times) * count(*) as total"
		." From tb_customer_service_history h "
		." JOIN tb_customer_service c on c.ID=h.SID "
		." JOIN tb_customer cus on cus.ID=c.CustID "
		." JOIN tb_branch b on b.ID=c.BranchID_Sale "
		." JOIN tb_branch bs on bs.ID= h.BranchID "
		." JOIN tb_product p on p.ID= c.ProductID "
		." where  1=1 and c.IsCancel = 0 and DATE_FORMAT(h.ApprovedDate,'%Y/%m/%d') between '".$datefrom."' and '".$dateto."'";	  
		
		if (isset($branch) && ($branch)!=0){
			$sql.=" and h.BranchID =".$branch;
		}else{
			$sql.=" and  h.BranchID IN ( SELECT BranchID FROM tb_customer_service_history Group BY BranchID ) ";
		}
		
		$sql.=" and h.BranchID <> c.BranchID_Sale ";
		$sql.=" Group by bs.ID, bs.BranchName , p.ProductName,cus.FirstName, cus.LastName , b.BranchName , c.Prices, c.Times";
		$sql.=" order by bs.BranchName ";
		//print $sql;
		$result = $this->ExecuteData($sql);
		$resultData = array();
 
		while($row = $result->FetchRow()){
			$sqlScrit =  " SELECT b.BranchName ,count(*)  as c , (c.Prices/c.Times) * count(*) as Total "
				." FROM tb_customer_service_history  h"
				." JOIN tb_customer_service c on c.ID=h.SID "
				." JOIN tb_branch b on b.ID=c.BranchID_Sale "
				." where 1=1 and c.IsCancel=0 and DATE_FORMAT(h.ApprovedDate,'%Y/%m/%d') between '".$datefrom."' and  '".$dateto."'"; 
	 			
			$sqlScrit .=" and h.BranchID <> c.BranchID_Sale and h.BranchID =".$row["ID"] ." Group by b.BranchName";
			//print $sqlScrit;
			$result2 = $this->ExecuteData($sqlScrit);
			$resultDetail = array();
		
			while($rows = $result2->FetchRow()){
				array_push($resultDetail,$rows);
			}
			
			$row["summary"]=$resultDetail;
			unset($resultDetail);
			$resultDetail=array();	
			
			array_push($resultData,$row);		
					
		}
		
		return $resultData;
	}
	//******************************
	// Account Report
	//******************************
	function AccountHeader(){
		return array('วันที่','ยอดรวม','ผลิตภัณฑ์','ทริทเมนท์','เซ็ท/คอร์ส','บัตรสมาชิก','เติมเงิน สมาร์ท', 'รับชำระ ลูกหนี้','ใช้บริการ คอร์สเดิม','ยอดรวม','เงินสด','บัตรเครดิต','สมาร์ท','ลูกหนี้ค้างชำระ','ผลิตภัณฑ์','ทริทเมนท์','เซ็ท/คอร์ส','บัตรสมาชิก','คูปอง','ตัดชำระ คอร์สเดิม');
	} 
	function AccountSale($datefrom,$dateto){
	
		$sql= "SELECT Distinct DATE_FORMAT(SaleDate,'%d/%m/%y') as 'Date'
			
				, COALESCE((SELECT SUM(a.Price * a.Amount)
				  from tb_pos_detail a
				  join tb_product d on d.id=a.Product and d.Type=1
				  join tb_pos p1 on p1.id=a.pos_id
				  where p1.IsCancel=0 and DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
				  ),0) as Product
				  
				, COALESCE((SELECT SUM(a.Discount)
				  from tb_pos_detail a
				  join tb_product d on d.id=a.Product and d.Type=1
				  join tb_pos p1 on p1.id=a.pos_id
				  where p1.IsCancel=0 and DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
				  ),0) as ProductDiscount
				  
				,COALESCE( (SELECT SUM(a.Price * a.Amount)
				  from tb_pos_detail a
				  join tb_product d on d.id=a.Product and d.Type=2
				  join tb_pos p1 on p1.id=a.pos_id
				  where p1.IsCancel=0 and DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
				),0) as Treatment  

				,COALESCE( (SELECT SUM(a.Discount)
				  from tb_pos_detail a
				  join tb_product d on d.id=a.Product and d.Type=2
				  join tb_pos p1 on p1.id=a.pos_id
				  where p1.IsCancel=0 and DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
				),0) as TreatmentDiscount 
				
				  ,COALESCE( (SELECT SUM(a.Price * a.Amount)
				  from tb_pos_detail a
				  join tb_product d on d.id=a.Product and d.Type in (3,4)
				  join tb_pos p1 on p1.id=a.pos_id
				  where p1.IsCancel=0 and DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')					
				 ),0) as 'Set'

				  ,COALESCE( (SELECT SUM(a.Discount)
				  from tb_pos_detail a
				  join tb_product d on d.id=a.Product and d.Type in (3,4)
				  join tb_pos p1 on p1.id=a.pos_id
				  where p1.IsCancel=0 and DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')						
				 ),0) as 'SetDiscount'
 
  
				, COALESCE((
						SELECT SUM((c.Prices/c.Times) * ch.Amount)
						from  tb_customer_service c 
						join tb_customer_service_history ch on ch.SID = c.ID
						where 1=1 and c.IsCancel =0 and DATE_FORMAT(ch.ApprovedDate,'%Y/%m/%d') = DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
				  ),0) as CourseCut
			  
				, COALESCE((SELECT SUM(a.Discount)
				  from tb_pos_detail a
					  join tb_product d on d.id=a.Product 
					  join tb_pos p2 on p2.id=a.pos_id
				  where p2.IsCancel=0 and DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			 
				  ),0) as Discount

				, COALESCE((SELECT SUM(a.Total)-sum(a.discount)
				  From tb_pos p2
				   join tb_pos_detail a on p2.id=a.pos_id
					  join tb_product d on d.id=a.Product
				  where p2.IsCancel=0 and DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
 				 
				 ),0) as TotalNet

				, COALESCE((SELECT SUM(py.PaymentAmount)
				  From tb_pos_payment py
				  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=1
				  where p2.IsCancel=0 and DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
 		
				 ),0) as PayCash
				
				, COALESCE((SELECT SUM(py.PaymentAmount)
				  From tb_pos_payment py
				  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=2
				  where p2.IsCancel=0 and DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
 
				  ),0) as PayCredit
			
				, COALESCE((SELECT SUM(py.PaymentAmount)
				  From tb_pos_payment py
				  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=3
				  where  p2.IsCancel=0 and DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
 		
					),0) as PaySmartPurse
		 
				 
				, COALESCE( ( SELECT sum(d.Amount) - pos.payAmt
					  FROM tb_creditsale t
					  JOIN tb_creditsale_detail d on d.Credit_ID=t.id  
					  join tb_pos p1 on p1.ID = t.PosID
					  Join (SELECT sum(pp.PaymentAmount) payAmt 
							,  DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') saledate
								FROM tb_pos p2 
							  join tb_pos_payment pp on pp.Pos_id=p2.id  and p2.RemainAmount <> 0 
							  where  p2.IsCancel=0 
							  group by  DATE_FORMAT(p2.SaleDate,'%Y/%m/%d')
							  ) pos on pos.SaleDate =DATE_FORMAT(p1.SaleDate,'%Y/%m/%d')
					  where  p1.IsCancel=0 and t.IsDeleted = 0 and DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') = DATE_FORMAT(p.SaleDate,'%Y/%m/%d')

					  ),0) as Credit
 
					  
				, COALESCE((SELECT  sum(t.RemainAmt ) 
					  FROM tb_creditsale t
					  Join tb_pos p2 on p2.id=t.PosID
					   where  p2.IsCancel=0 and t.IsDeleted = 0 and  DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
 					 ),0) as CreditRemain	
					 				   
				, COALESCE((SELECT  SUM(s.Amount) amtSmartpurse
						From tb_smartpurse s 
						Where IsDeleted=0 and   DATE_FORMAT(s.CreatedDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
 				    ),0) as SmartPurse	
					
				From tb_pos p 
				Where 1=1  AND p.IsCancel = 0  
				And DATE_FORMAT(p.SaleDate,'%Y/%m/%d')  Between '".$datefrom."' and  '".$dateto."'";  
				$sql.= " group by p.SaleDate ";	
			// return $sql;
				 $result = $this->ExecuteData($sql);		
				 return $result;				
				 
	}
	//******************************
	// Daily Sale Report
	//******************************		
	function DailySaleHeader(){
		return array('วันที่','สินค้า','ส่วนลด','Treatment','ส่วนลด','Set','ส่วนลด', 'Course','ส่วนลด','ค่าตัดคอร์ส','รวมส่วนลด','ยอดรวม','รายได้รวม','เติมเงิน','เงินสด','เครดิต','สมาร์ทเพิร์ท','ชำระหนี้','ช่องลูกหนี้');
	}
	function DailySaleReport($datefrom,$dateto, $branch=0){
	
		$sql=" Select p.Branch, b.BranchName from tb_pos p join tb_branch b on b.id=p.Branch"
			." where 1=1 and p.IsCancel = 0 " 
			." And  DATE_FORMAT(p.SaleDate,'%Y/%m/%d')  Between '".$datefrom."' and  '".$dateto."'"; 
	
		if (isset($branch) && ($branch)!=0){
			$sql.="  and p.Branch =".$branch;
		}
		$sql.= " Group by p.Branch,b.BranchName  Order by b.BranchName ";
		$result = $this->ExecuteData($sql);
		
		$resultData = array();
		$resultSummary=array();
		while($row = $result->FetchRow()){
			$result_detail = $this->SQL_DailyReport($datefrom,$dateto,$row['Branch']);
			
			while($row_detail= $result_detail->FetchRow()){
				array_push($resultSummary,$row_detail);
			}
				
			$row["summary"]=$resultSummary;
			array_push($resultData,$row);		
		
			unset($resultSummary);
			$resultSummary=array();	
 
		}
		
		return $resultData;
	}
	function SQL_DailyReport($datefrom,$dateto, $bid=0){
		$sql= "SELECT DATE_FORMAT(SaleDate,'%d/%m/%y') as 'Date'
			, COALESCE((SELECT SUM(a.Price * a.Amount)
			  from tb_pos_detail a
			  join tb_product d on d.id=a.Product and d.Type=1
			  join tb_pos p1 on p1.ID=a.Pos_id
			  where DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			   and p1.Branch = p.Branch
			  and p1.IsCancel = 0
			  ),0) as Product
			  
			, COALESCE((SELECT SUM(a.Discount)
			  from tb_pos_detail a
			  join tb_product d on d.id=a.Product and d.Type=1
			  join tb_pos p1 on p1.id=a.Pos_id
			  where DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			  and p1.Branch = p.Branch
			  and p1.IsCancel = 0
			  ),0) as ProductDiscount  
			  
			,COALESCE( (SELECT SUM(a.Price * a.Amount)
			  from tb_pos_detail a
			  join tb_product d on d.id=a.Product and d.Type=2
			  join tb_pos p1 on p1.id=a.Pos_id
			  where DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			  and p1.Branch = p.Branch			
			  and p1.IsCancel = 0	  
			),0) as Treatment  
			
			 ,COALESCE( (SELECT SUM(a.Discount)
			  from tb_pos_detail a
			  join tb_product d on d.id=a.Product and d.Type=2
			  join tb_pos p1 on p1.id=a.Pos_id
			  where DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			  and p1.Branch = p.Branch			
			  and p1.IsCancel = 0
			),0) as TreatmentDiscount
			
			  ,COALESCE( (SELECT SUM(a.Price * a.Amount)
			  from tb_pos_detail a
			  join tb_product d on d.id=a.Product and d.Type=3
			  join tb_pos p1 on p1.id=a.Pos_id
			  where DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			  and p1.Branch = p.Branch			
			  and p1.IsCancel = 0	  
			 ),0) as 'Set'
						  ,COALESCE( (SELECT SUM(a.Discount)
			  from tb_pos_detail a
			  join tb_product d on d.id=a.Product and d.Type=3
			  join tb_pos p1 on p1.id=a.Pos_id
			  where DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			  and p1.Branch = p.Branch			
			  and p1.IsCancel = 0	  
			 ),0) as 'SetDiscount' 
			 
			  , COALESCE((SELECT SUM(a.Price * a.Amount)
			  from tb_pos_detail a
			  join tb_product d on d.id=a.Product and d.Type=4
			  join tb_pos p2 on p2.id=a.Pos_id
			  where DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			  and p2.Branch = p.Branch			
			  and p2.IsCancel = 0	  
			   ),0) as Course
			   
			  , COALESCE((SELECT SUM(a.Discount)
			  from tb_pos_detail a
			  join tb_product d on d.id=a.Product and d.Type=4
			  join tb_pos p2 on p2.id=a.Pos_id
			  where DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			  and p2.Branch = p.Branch		
			  and p2.IsCancel = 0		  
			   ),0) as CourseDiscount
			   
			, COALESCE((
					SELECT SUM((c.Prices/c.Times) * ch.Amount)
					from  tb_customer_service c 
					join tb_customer_service_history ch on ch.SID = c.ID
					where DATE_FORMAT(ch.ApprovedDate,'%Y/%m/%d') = DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
					and ch.BranchID =  p.Branch	and c.IsCancel = 0
			  ),0) as CourseCut

			  , COALESCE((SELECT SUM(a.Discount)
			  from tb_pos_detail a
			  join tb_product d on d.id=a.Product 
			  join tb_pos p2 on p2.id=a.Pos_id
			  where DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
				and p2.Branch = p.Branch	
			  and p2.IsCancel = 0				
			  ),0) as Discount

			, COALESCE((SELECT  SUM(a.Total)-sum(a.discount)
			  From tb_pos p2
			  join tb_pos_detail a on p2.id=a.Pos_id
			  join tb_product d on d.id=a.Product 
			  where DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			 and p2.Branch = p.Branch	
			  and p2.IsCancel = 0			 
			 ),0) as TotalNet

			, COALESCE((SELECT SUM(py.PaymentAmount)
			  From tb_pos_payment py
			  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=1
			  where DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			  and p2.Branch = p.Branch		
			  and p2.IsCancel = 0			  
			 ),0) as PayCash
			
			, COALESCE((SELECT SUM(py.PaymentAmount)
			  From tb_pos_payment py
			  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=2
			  where DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			 and p2.Branch = p.Branch	
			  and p2.IsCancel = 0	
			  ),0) as PayCredit
		
			, COALESCE((SELECT SUM(py.PaymentAmount)
			  From tb_pos_payment py
			  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=3
			  where DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			 and p2.Branch = p.Branch		
			  and p2.IsCancel = 0				 
				),0) as PaySmartPurse

			, COALESCE( ( SELECT sum(d.Amount) - pos.payAmt
			  FROM tb_creditsale t
			  JOIN tb_creditsale_detail d on d.Credit_ID=t.id  
			  join tb_pos p1 on p1.ID = t.PosID
			  Join (SELECT p2.Branch
                    , sum(pp.PaymentAmount) payAmt 
                    ,  DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') saledate
						FROM tb_pos p2 
        			  join tb_pos_payment pp on pp.Pos_id=p2.id  and p2.RemainAmount <> 0 and p2.IsCancel = 0
    			      group by p2.Branch,  DATE_FORMAT(p2.SaleDate,'%Y/%m/%d')
        			  ) pos on pos.Branch = p1.Branch and pos.SaleDate =DATE_FORMAT(p1.SaleDate,'%Y/%m/%d')
			  where t.IsDeleted = 0 and  DATE_FORMAT(p1.SaleDate,'%Y/%m/%d') = DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			   and p.Branch = p1.Branch		
			  and p1.IsCancel = 0	
			  ),0) as Credit

			, COALESCE((SELECT  sum(t.RemainAmt ) 
			  FROM tb_creditsale t
			  Join tb_pos p2 on p2.id=t.PosID
			  where t.IsDeleted = 0 and DATE_FORMAT(p2.SaleDate,'%Y/%m/%d') =DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
			  and p2.Branch = p.Branch		
			  and p2.IsCancel = 0
			 ),0) as Remain
			  
			, COALESCE( (
					SELECT  SUM(s.Amount)  
						From tb_smartpurse s 
						Where IsDeleted = 0 and p.Branch=s.BranchID and DATE_FORMAT(s.CreatedDate,'%Y/%m/%d')  Between '".$datefrom."' and  '".$dateto."'
						Group by  DATE_FORMAT(s.CreatedDate,'%Y/%m/%d') 
			) ,0) smartpurse
			, SaleDate as _date
			
			From tb_pos p 
		 
			  where 1=1 AND p.IsCancel = 0   And  
			  DATE_FORMAT(p.SaleDate,'%Y/%m/%d')  Between '".$datefrom."' and  '".$dateto."'";  
			
			$b='';
			if (isset($bid) && ($bid)!=0){
				$sql.=" and p.Branch =".$bid;
				$b = " and p.Branch =".$bid;
			}
			
			$sql.= " group by DATE_FORMAT(SaleDate,'%d/%m/%Y')";
			
			// $sql .= " UNION ";
			
			// $sql .= " SELECT DATE_FORMAT(s.CreatedDate,'%d/%m/%y')  CreatedDate                                        
					// ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0                                                                                              
					// ,(s.Amount) smartpurse  ,   CreatedDate as _date                                                               
					// From tb_smartpurse s                                                                            
					// Where 1=1  and s.IsDeleted = 0                                                                                   
					// AND DATE_FORMAT(s.CreatedDate,'%Y/%m/%d') Between '".$datefrom."' and  '".$dateto."'            
					// And NOT EXISTS (SELECT DATE_FORMAT(p.SaleDate,'%Y/%m/%d')                                       
											// FROM tb_pos p                                                                 
											// Where 1=1 ".$b."                                                                    
											// AND DATE_FORMAT(p.SaleDate,'%Y/%m/%d') Between '".$datefrom."' and  '".$dateto."'
											// AND DATE_FORMAT(s.CreatedDate,'%Y/%m/%d') = DATE_FORMAT(p.SaleDate,'%Y/%m/%d')
										// )                                                                                
					// Group by DATE_FORMAT(s.CreatedDate,'%d/%m/%y')  ";
			$sql.=" order by _date ";
			
			$result = $this->ExecuteData($sql);
			
			return $result;
	}
	//******************************
	// Monthly Sale Report
	//******************************	
	function MonthlySaleHeader(){
		//return array('ลำดับ','เดือน/ปี','สินค้า','Treatment','Set' , 'Course','ค่าตัดคอร์ส','ส่วนลด','ยอดรวม','รายได้รวม','เงินสด','เครดิต','สมาร์ทเพิร์ท','เงินสด','เครดิต','สมาร์ทเพิร์ท');
		return array('เดือน/ปี','สินค้า','ส่วนลด','Treatment','ส่วนลด','Set','ส่วนลด', 'Course','ส่วนลด','ค่าตัดคอร์ส','รวมส่วนลด','ยอดรวม','รายได้รวม','เติมเงิน','เงินสด','เครดิต','สมาร์ทเพิร์ท','ชำระหนี้','ช่องลูกหนี้');
	}
	function MonthlySaleReport($mfrom,$mto,$yfrom,$yto, $branch=0){
	
		$sql=" Select p.Branch, b.BranchName from tb_pos p join tb_branch b on b.id=p.Branch"
			." where 1=1 and p.IsCancel = 0  " 
			." And DATE_FORMAT(p.SaleDate,'%m') BETWEEN ".$mfrom." and ".$mto."  AND DATE_FORMAT(p.SaleDate,'%Y')  Between ".$yfrom." and  ".$yto; 
		//print $sql;
		if (isset($branch) && ($branch)!=0){
			$sql.="  and p.Branch =".$branch;
		}
		$sql.= " Group by p.Branch,b.BranchName  Order by b.BranchName ";
		$result = $this->ExecuteData($sql);
		
		$resultData = array();
		$resultSummary=array();
		while($row = $result->FetchRow()){
			$result_detail = $this->SQL_MonthlyReport($mfrom,$mto,$yfrom,$yto, $row['Branch']);
			
			while($row_detail= $result_detail->FetchRow()){
				array_push($resultSummary,$row_detail);
			}
				
			$row["summary"]=$resultSummary;
			array_push($resultData,$row);		
		
			unset($resultSummary);
			$resultSummary=array();	
 
		}
		
		return $resultData;
	}
	function SQL_MonthlyReport($mfrom,$mto,$yfrom,$yto,  $bid=0){
	
	$sql= "SELECT DATE_FORMAT(SaleDate,'%m/%y') as 'Date'
		, COALESCE((SELECT SUM(a.Price * a.Amount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=1
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		   and p1.Branch = p.Branch
		   and p1.IsCancel = 0 
		  ),0) as Product
		  
		, COALESCE((SELECT SUM(a.Discount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=1
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		   and p1.Branch = p.Branch
		   and p1.IsCancel = 0 		   
		  ),0) as ProductDiscount
		  
		,COALESCE( (SELECT SUM(a.Price * a.Amount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=2
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p1.Branch = p.Branch		
		   and p1.IsCancel = 0 		  
		),0) as Treatment  

		,COALESCE( (SELECT SUM(a.Discount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=2
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p1.Branch = p.Branch			
		   and p1.IsCancel = 0 		  
		),0) as TreatmentDiscount 
		
		  ,COALESCE( (SELECT SUM(a.Price * a.Amount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=3
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p1.Branch = p.Branch	 and p1.IsCancel = 0 
		 ),0) as 'Set'

		  ,COALESCE( (SELECT SUM(a.Discount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=3
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p1.Branch = p.Branch		 and p1.IsCancel = 0 					
		 ),0) as 'SetDiscount'
		 
		  , COALESCE((SELECT SUM(a.Price * a.Amount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=4
		  join tb_pos p2 on p2.id=a.pos_id
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p2.Branch = p.Branch	  and p2.IsCancel = 0 			  
		   ),0) as Course
		   
		  , COALESCE((SELECT SUM(a.Discount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=4
		  join tb_pos p2 on p2.id=a.pos_id
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p2.Branch = p.Branch  and p2.IsCancel = 0 				  
		   ),0) as CourseDiscount   
 
			, COALESCE((
					SELECT SUM((c.Prices/c.Times) * ch.Amount)
					from  tb_customer_service c 
					join tb_customer_service_history ch on ch.SID = c.ID
					where DATE_FORMAT(ch.ApprovedDate,'%Y/%m') = DATE_FORMAT(p.SaleDate,'%Y/%m')
					and ch.BranchID = p.Branch and c.IsCancel = 0		
			  ),0) as CourseCut		  
		  
		, COALESCE((SELECT SUM(a.Discount)
		  from tb_pos_detail a
			  join tb_product d on d.id=a.Product 
			  join tb_pos p2 on p2.id=a.pos_id
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
			and p2.Branch = p.Branch	 and p2.IsCancel = 0 			 
		  ),0) as Discount

		, COALESCE((SELECT SUM(a.Total)-sum(a.discount)
		  From tb_pos p2
		   join tb_pos_detail a on p2.id=a.pos_id
			  join tb_product d on d.id=a.Product
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		 and p2.Branch = p.Branch and p2.IsCancel = 0 					 
		 ),0) as TotalNet

		, COALESCE((SELECT SUM(py.PaymentAmount)
		  From tb_pos_payment py
		  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=1
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p2.Branch = p.Branch		and p2.IsCancel = 0 			
		 ),0) as PayCash
		
		, COALESCE((SELECT SUM(py.PaymentAmount)
		  From tb_pos_payment py
		  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=2
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		 and p2.Branch = p.Branch	and p2.IsCancel = 0 	
		  ),0) as PayCredit
	
		, COALESCE((SELECT SUM(py.PaymentAmount)
		  From tb_pos_payment py
		  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=3
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		 and p2.Branch = p.Branch	 and p2.IsCancel = 0 			
			),0) as PaySmartPurse
 
		 
		, COALESCE( ( SELECT sum(d.Amount) - pos.payAmt
			  FROM tb_creditsale t
			  JOIN tb_creditsale_detail d on d.Credit_ID=t.id  
			  join tb_pos p1 on p1.ID = t.PosID
			  Join (SELECT p2.Branch
                    , sum(pp.PaymentAmount) payAmt 
                    ,  DATE_FORMAT(p2.SaleDate,'%Y/%m') saledate
						FROM tb_pos p2 
        			  join tb_pos_payment pp on pp.Pos_id=p2.id  and p2.RemainAmount <> 0 and p2.IsCancel = 0 	
    			      group by p2.Branch,  DATE_FORMAT(p2.SaleDate,'%Y/%m')
        			  ) pos on pos.Branch = p1.Branch and pos.SaleDate =DATE_FORMAT(p1.SaleDate,'%Y/%m')
			  where t.IsDeleted = 0 and  DATE_FORMAT(p1.SaleDate,'%Y/%m') = DATE_FORMAT(p.SaleDate,'%Y/%m')
			   and p.Branch = p1.Branch	and p1.IsCancel = 0 		
			  ),0) as Credit
			  
		, COALESCE((SELECT  sum(t.RemainAmt ) 
			  FROM tb_creditsale t
			  Join tb_pos p2 on p2.id=t.PosID
			   where t.IsDeleted = 0 and DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
			  and p2.Branch = p.Branch	 and p2.IsCancel = 0 		
			 ),0) as Remain	
			 
		, COALESCE(  (
				 SELECT SUM(s.Amount) 
 				From tb_smartpurse s 
				Where IsDeleted=0 and DATE_FORMAT(s.CreatedDate,'%m') Between ".$mfrom." and ".$mto." and DATE_FORMAT(s.CreatedDate,'%Y') Between ".$yfrom." and  ".$yto."
				 And  p.Branch=s.BranchID
				Group by s.BranchID, DATE_FORMAT(s.CreatedDate,'%Y/%m') 
		) ,0) smartpurse 
		   
		, SaleDate as _date
		   
		From tb_pos p 
 		
		Where 1=1 
		AND p.IsCancel = 0  
		AND DATE_FORMAT(p.SaleDate,'%m') BETWEEN ".$mfrom." and ".$mto."  AND DATE_FORMAT(p.SaleDate,'%Y') Between ".$yfrom." and  ".$yto; 
		

		if (isset($bid) && ($bid)!=0){
			$sql.=" and p.Branch =".$bid;
			$b = " and p.Branch =".$bid;
		}
		$sql.= " group by DATE_FORMAT(SaleDate,'%m/%Y')";
		// $sql .= " UNION ";

		// $sql .="SELECT DATE_FORMAT(s.CreatedDate,'%m/%y')  CreatedDate                                        
			// ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0                                                                                            
			// ,  (s.Amount) smartpurse , CreatedDate as _date                                                               
			// From tb_smartpurse s                                                                            
			// Where 1=1  and IsDeleted = 0                                                                                      
				// AND DATE_FORMAT(s.CreatedDate,'%m') Between ".$mfrom." and ".$mto." and DATE_FORMAT(s.CreatedDate,'%Y') BETWEEN ".$yfrom." and  ".$yto."       
					// And NOT EXISTS (SELECT DATE_FORMAT(p.SaleDate,'%Y/%m/%d')                                       
						// FROM tb_pos p                                                                 
						// Where 1=1                                                                     
						// AND DATE_FORMAT(p.SaleDate,'%m') Between ".$mfrom." and ".$mto." and DATE_FORMAT(p.SaleDate,'%Y') BETWEEN ".$yfrom." and  ".$yto."
						// AND DATE_FORMAT(s.CreatedDate,'%Y/%m') = DATE_FORMAT(p.SaleDate,'%Y/%m')
					// )                                                                                
			// Group by DATE_FORMAT(s.CreatedDate,'%m/%y') ";
		
		$sql.=" order by _date ";
		
		$result = $this->ExecuteData($sql);
		
		return $result;
	}
	//******************************
	// Quarter Sale Report
	//******************************	
	function QuarterSaleHeader(){
		//return array('ลำดับ','Q/ปี','สินค้า','Treatment','Set' , 'Course','ค่าตัดคอร์ส','ส่วนลด','ยอดรวม','รายได้รวม','เงินสด','เครดิต','สมาร์ทเพิร์ท','เงินสด','เครดิต','สมาร์ทเพิร์ท');
	return array('Q/ปี','สินค้า','ส่วนลด','Treatment','ส่วนลด','Set','ส่วนลด', 'Course','ส่วนลด','ค่าตัดคอร์ส','รวมส่วนลด','ยอดรวม','รายได้รวม','เติมเงิน','เงินสด','เครดิต','สมาร์ทเพิร์ท','ชำระหนี้','ช่องลูกหนี้');
	}
	function QuarterSaleReport($qfrom, $qto, $yfrom,$yto, $branch=0){
	
		$sql=" Select p.Branch, b.BranchName from tb_pos p join tb_branch b on b.id=p.Branch"
			." where 1=1 and p.IsCancel = 0 	" 
			."  And DATE_FORMAT(p.SaleDate,'%m') BETWEEN ".$qfrom." and ".$qto."  AND  DATE_FORMAT(p.SaleDate,'%Y')  Between ".$yfrom." and  ".$yto; 
		 
		if (isset($branch) && ($branch)!=0){
			$sql.="  and p.Branch =".$branch;
		}
		$sql.= " Group by p.Branch,b.BranchName  Order by b.BranchName ";
	
		$result = $this->ExecuteData($sql);
		
		$resultData = array();
		$resultSummary=array();
		while($row = $result->FetchRow()){
			$result_detail = $this->SQL_QuarterReport($qfrom, $qto,  $yfrom,$yto, $row['Branch']);
			
			while($row_detail= $result_detail->FetchRow()){
				array_push($resultSummary,$row_detail);
			}
				
			$row["summary"]=$resultSummary;
			array_push($resultData,$row);		
		
			unset($resultSummary);
			$resultSummary=array();	
 
		}
		
		return $resultData;
	}
	function SQL_QuarterReport( $qfrom ,$qto, $yfrom,$yto,  $bid=0){
	
	$sql= "SELECT Case when DATE_FORMAT(SaleDate,'%m') >= 1 and DATE_FORMAT(SaleDate,'%m') <=3 then 'Q1' 
		  when DATE_FORMAT(SaleDate,'%m') >=4 and DATE_FORMAT(SaleDate,'%m') <=6 then 'Q2'
		  when DATE_FORMAT(SaleDate,'%m') >=7 and DATE_FORMAT(SaleDate,'%m') <=9 then 'Q3'
		  when DATE_FORMAT(SaleDate,'%m') >=10 and DATE_FORMAT(SaleDate,'%m') <=12 then 'Q4' end as Quarter
	
		, DATE_FORMAT(SaleDate,'%y') as 'Date'
		
		, COALESCE((SELECT SUM(a.Price * a.Amount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=1
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		   and p1.Branch = p.Branch and p1.IsCancel = 0 	
		  ),0) as Product
		  
		, COALESCE((SELECT SUM(a.Discount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=1
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		   and p1.Branch = p.Branch  and p1.IsCancel = 0 	
		  ),0) as ProductDiscount
		  
		,COALESCE( (SELECT SUM(a.Price * a.Amount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=2
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p1.Branch = p.Branch	 and p1.IsCancel = 0 		 
		),0) as Treatment  

		,COALESCE( (SELECT SUM(a.Discount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=2
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p1.Branch = p.Branch		 and p1.IsCancel = 0 	 	
		),0) as TreatmentDiscount 
		
		  ,COALESCE( (SELECT SUM(a.Price * a.Amount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=3
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p1.Branch = p.Branch	  and p1.IsCancel = 0 							
		 ),0) as 'Set'

		  ,COALESCE( (SELECT SUM(a.Discount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=3
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p1.Branch = p.Branch		 and p1.IsCancel = 0 						
		 ),0) as 'SetDiscount'
		 
		  , COALESCE((SELECT SUM(a.Price * a.Amount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=4
		  join tb_pos p2 on p2.id=a.pos_id
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p2.Branch = p.Branch		 and p2.IsCancel = 0 			  
		   ),0) as Course
		   
		  , COALESCE((SELECT SUM(a.Discount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=4
		  join tb_pos p2 on p2.id=a.pos_id
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p2.Branch = p.Branch	  and p2.IsCancel = 0 				  
		   ),0) as CourseDiscount   
  
			, COALESCE((
					SELECT SUM((c.Prices/c.Times) * ch.Amount)
					from  tb_customer_service c 
					join tb_customer_service_history ch on ch.SID = c.ID
					where DATE_FORMAT(ch.ApprovedDate,'%Y/%m') = DATE_FORMAT(p.SaleDate,'%Y/%m')
					and ch.BranchID = p.Branch and c.IsCancel = 0		
			  ),0) as CourseCut			  
		  
		, COALESCE((SELECT SUM(a.Discount)
		 from tb_pos_detail a
			  join tb_product d on d.id=a.Product 
			  join tb_pos p2 on p2.id=a.pos_id
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
			and p2.Branch = p.Branch	 and p2.IsCancel = 0 				 
		  ),0) as Discount

		, COALESCE((SELECT SUM(a.Total)-sum(a.discount)
			  From tb_pos p2
			  join tb_pos_detail a on p2.id=a.pos_id
			  join tb_product d on d.id=a.Product 
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		 and p2.Branch = p.Branch	 and p2.IsCancel = 0 					 
		 ),0) as TotalNet

		, COALESCE((SELECT SUM(py.PaymentAmount)
		  From tb_pos_payment py
		  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=1
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		  and p2.Branch = p.Branch		 and p2.IsCancel = 0 			
		 ),0) as PayCash
		
		, COALESCE((SELECT SUM(py.PaymentAmount)
		  From tb_pos_payment py
		  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=2
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		 and p2.Branch = p.Branch	and p2.IsCancel = 0 
		  ),0) as PayCredit
	
		, COALESCE((SELECT SUM(py.PaymentAmount)
		  From tb_pos_payment py
		  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=3
		  where DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
		 and p2.Branch = p.Branch	and p2.IsCancel = 0 		
			),0) as PaySmartPurse
 
		 
		, COALESCE( ( SELECT sum(d.Amount) - pos.payAmt
			  FROM tb_creditsale t
			  JOIN tb_creditsale_detail d on d.Credit_ID=t.id  
			  join tb_pos p1 on p1.ID = t.PosID
			  Join (SELECT p2.Branch
                    , sum(pp.PaymentAmount) payAmt 
                    ,  DATE_FORMAT(p2.SaleDate,'%Y/%m') saledate
						FROM tb_pos p2 
        			  join tb_pos_payment pp on pp.Pos_id=p2.id  and p2.RemainAmount <> 0 and p2.IsCancel = 0 
    			      group by p2.Branch,  DATE_FORMAT(p2.SaleDate,'%Y/%m')
        			  ) pos on pos.Branch = p1.Branch and pos.SaleDate =DATE_FORMAT(p1.SaleDate,'%Y/%m')
			  where t.IsDeleted = 0 and  DATE_FORMAT(p1.SaleDate,'%Y/%m') = DATE_FORMAT(p.SaleDate,'%Y/%m')
			   and p.Branch = p1.Branch		and p1.IsCancel = 0 
			  ),0) as Credit
			  
		, COALESCE((SELECT  sum(t.RemainAmt ) 
			  FROM tb_creditsale t
			  Join tb_pos p2 on p2.id=t.PosID
			   where t.IsDeleted = 0 and  DATE_FORMAT(p2.SaleDate,'%Y/%m') =DATE_FORMAT(p.SaleDate,'%Y/%m')
			  and p2.Branch = p.Branch		and p2.IsCancel = 0 
			 ),0) as Remain	
			 
		, COALESCE((   
				SELECT SUM(s.Amount) 
 				From tb_smartpurse s 
				Where IsDeleted=0 and  DATE_FORMAT(s.CreatedDate,'%m') Between ".$qfrom." and ".$qto." and DATE_FORMAT(s.CreatedDate,'%Y') Between ".$yfrom." and  ".$yto."
				 And  p.Branch=s.BranchID
				Group by s.BranchID, DATE_FORMAT(s.CreatedDate,'%Y/%m') 
		  ) ,0) smartpurse 
		  
		, SaleDate as _date
		
		  From tb_pos p 
 
		  where 1=1 and p.IsCancel = 0 
		  and DATE_FORMAT(p.SaleDate,'%m') BETWEEN ".$qfrom." and ".$qto."  and DATE_FORMAT(p.SaleDate,'%Y')  Between ".$yfrom." and  ".$yto; 
		

		if (isset($bid) && ($bid)!=0){
			$sql.=" and p.Branch =".$bid;
			$b = " and p.Branch =".$bid;
		}
		$sql.= " group by DATE_FORMAT(SaleDate,'%Y') , Case when DATE_FORMAT(SaleDate,'%m') >= 1 and DATE_FORMAT(SaleDate,'%m') <=3 then 'Q1' 
		  when DATE_FORMAT(SaleDate,'%m') >=4 and DATE_FORMAT(SaleDate,'%m') <=6 then 'Q2'
		  when DATE_FORMAT(SaleDate,'%m') >=7 and DATE_FORMAT(SaleDate,'%m') <=9 then 'Q3'
		  when DATE_FORMAT(SaleDate,'%m') >=10 and DATE_FORMAT(SaleDate,'%m') <=12 then 'Q4' end ";


		// $sql .= " UNION ";

		// $sql .=" SELECT Case when DATE_FORMAT(CreatedDate,'%m') >= 1 and DATE_FORMAT(CreatedDate,'%m') <=3 then 'Q1' 
			  // when DATE_FORMAT(CreatedDate,'%m') >=4 and DATE_FORMAT(CreatedDate,'%m') <=6 then 'Q2'
			  // when DATE_FORMAT(CreatedDate,'%m') >=7 and DATE_FORMAT(CreatedDate,'%m') <=9 then 'Q3'
			  // when DATE_FORMAT(CreatedDate,'%m') >=10 and DATE_FORMAT(CreatedDate,'%m') <=12 then 'Q4' end as Quarter

			// , DATE_FORMAT(CreatedDate,'%y') as 'Date' 			,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0                                                                                            
			// ,  (s.Amount) smartpurse , CreatedDate as _date                                                               
			// From tb_smartpurse s                                                                            
			// Where 1=1    and s.IsDeleted =0                                                                            
				// AND DATE_FORMAT(s.CreatedDate,'%m') Between ".$qfrom." and ".$qto." and DATE_FORMAT(s.CreatedDate,'%Y') BETWEEN ".$yfrom." and  ".$yto."       
					// And NOT EXISTS (SELECT DATE_FORMAT(p.SaleDate,'%Y/%m/%d')                                       
						// FROM tb_pos p                                                                 
						// Where 1=1                                                                     
						// AND DATE_FORMAT(p.SaleDate,'%m') Between ".$qfrom." and ".$qto." and DATE_FORMAT(p.SaleDate,'%Y') BETWEEN ".$yfrom." and  ".$yto."
						// AND DATE_FORMAT(s.CreatedDate,'%Y/%m') = DATE_FORMAT(p.SaleDate,'%Y/%m')
					// )                                                                                
			// group by DATE_FORMAT(CreatedDate,'%Y') , Case when DATE_FORMAT(CreatedDate,'%m') >= 1 and DATE_FORMAT(CreatedDate,'%m') <=3 then 'Q1' 
		  // when DATE_FORMAT(CreatedDate,'%m') >=4 and DATE_FORMAT(CreatedDate,'%m') <=6 then 'Q2'
		  // when DATE_FORMAT(CreatedDate,'%m') >=7 and DATE_FORMAT(CreatedDate,'%m') <=9 then 'Q3'
		  // when DATE_FORMAT(CreatedDate,'%m') >=10 and DATE_FORMAT(CreatedDate,'%m') <=12 then 'Q4' end ";
		
		
		$sql.=" order by _date ";
			//print $sql; 
		$result = $this->ExecuteData($sql);
		
		return $result;
	}	
	
	//******************************
	// Year Sale Report
	//******************************		
	function YearSaleHeader(){	
		//return array('ลำดับ','ปี','สินค้า','Treatment','Set' , 'Course','ค่าตัดคอร์ส','ส่วนลด','ยอดรวม','รายได้รวม','เงินสด','เครดิต','สมาร์ทเพิร์ท','เงินสด','เครดิต','สมาร์ทเพิร์ท');
		return array('ปี','สินค้า','ส่วนลด','Treatment','ส่วนลด','Set','ส่วนลด', 'Course','ส่วนลด','ค่าตัดคอร์ส','รวมส่วนลด','ยอดรวม','รายได้รวม','เติมเงิน','เงินสด','เครดิต','สมาร์ทเพิร์ท','ชำระหนี้','ช่องลูกหนี้');
	}
	function YearSaleReport( $yfrom,$yto, $branch=0){
	
		$sql=" Select p.Branch, b.BranchName from tb_pos p join tb_branch b on b.id=p.Branch"
			." where 1=1  and p.IsCancel = 0  " 
			." And  DATE_FORMAT(p.SaleDate,'%Y')  Between ".$yfrom." and  ".$yto; 
		//print $sql;
		if (isset($branch) && ($branch)!=0){
			$sql.="  and p.Branch =".$branch;
		}
		$sql.= " Group by p.Branch,b.BranchName  Order by b.BranchName ";
		$result = $this->ExecuteData($sql);
		
		$resultData = array();
		$resultSummary=array();
		while($row = $result->FetchRow()){
			$result_detail = $this->SQL_YearReport( $yfrom,$yto, $row['Branch']);
			
			while($row_detail= $result_detail->FetchRow()){
				array_push($resultSummary,$row_detail);
			}
				
			$row["summary"]=$resultSummary;
			array_push($resultData,$row);		
		
			unset($resultSummary);
			$resultSummary=array();	
 
		}
		
		return $resultData;
	}
	function SQL_YearReport( $yfrom,$yto,  $bid=0){
	
	$sql= "SELECT DATE_FORMAT(SaleDate,'%Y') as 'Date'
	
		, COALESCE((SELECT SUM(a.Price * a.Amount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=1
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
		   and p1.Branch = p.Branch  and p1.IsCancel = 0 
		  ),0) as Product
		  
		  , COALESCE((SELECT SUM(a.Discount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=1
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
		   and p1.Branch = p.Branch  and p1.IsCancel = 0 
		  ),0) as ProductDiscount
		  
		,COALESCE( (SELECT SUM(a.Price * a.Amount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=2
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
		  and p1.Branch = p.Branch	  and p1.IsCancel = 0 		
		),0) as Treatment  

		,COALESCE( (SELECT SUM(a.Discount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=2
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
		  and p1.Branch = p.Branch	  and p1.IsCancel = 0 		
		),0) as TreatmentDiscount
		
		  ,COALESCE( (SELECT SUM(a.Price * a.Amount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=3
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
		  and p1.Branch = p.Branch	  and p1.IsCancel = 0 						
		 ),0) as 'Set'
		 
		 ,COALESCE( (SELECT SUM(a.Discount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=3
		  join tb_pos p1 on p1.id=a.pos_id
		  where DATE_FORMAT(p1.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
		  and p1.Branch = p.Branch  and p1.IsCancel = 0 							
		 ),0) as 'SetDiscount'
		  
		  , COALESCE((SELECT SUM(a.Price * a.Amount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=4
		  join tb_pos p2 on p2.id=a.pos_id
		  where DATE_FORMAT(p2.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
		  and p2.Branch = p.Branch	  and p2.IsCancel = 0 			  
		   ),0) as Course

		     , COALESCE((SELECT SUM(a.Discount)
		  from tb_pos_detail a
		  join tb_product d on d.id=a.Product and d.Type=4
		  join tb_pos p2 on p2.id=a.pos_id
		  where DATE_FORMAT(p2.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
		  and p2.Branch = p.Branch	   and p2.IsCancel = 0 				  
		   ),0) as Course
		   
 
			, COALESCE((
					SELECT SUM((c.Prices/c.Times) * ch.Amount)
					from  tb_customer_service c 
					join tb_customer_service_history ch on ch.SID = c.ID
					where DATE_FORMAT(ch.ApprovedDate,'%Y') = DATE_FORMAT(p.SaleDate,'%Y')
					and ch.BranchID = p.Branch and c.IsCancel = 0		
			  ),0) as CourseCut		
			  
		, COALESCE((SELECT SUM(p2.Discount)
		  From tb_pos p2
		  where DATE_FORMAT(p2.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
			and p2.Branch = p.Branch	  and p2.IsCancel = 0 				 
		  ),0) as Discount

		, COALESCE((SELECT SUM(a.Total)-sum(a.discount)
			  From tb_pos p2
			  join tb_pos_detail a on p2.id=a.pos_id
			  join tb_product d on d.id=a.Product 
		  where DATE_FORMAT(p2.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
		 and p2.Branch = p.Branch	  and p2.IsCancel = 0 					 
		 ),0) as TotalNet

		, COALESCE((SELECT SUM(py.PaymentAmount)
		  From tb_pos_payment py
		  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=1
		  where DATE_FORMAT(p2.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
		  and p2.Branch = p.Branch		  and p2.IsCancel = 0 			
		 ),0) as PayCash
		
		, COALESCE((SELECT SUM(py.PaymentAmount)
		  From tb_pos_payment py
		  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=2
		  where DATE_FORMAT(p2.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
		 and p2.Branch = p.Branch	  and p2.IsCancel = 0 	
		  ),0) as PayCredit
	
		, COALESCE((SELECT SUM(py.PaymentAmount)
		  From tb_pos_payment py
		  join tb_pos p2 on p2.id=py.Pos_id and py.PaymentType=3
		  where DATE_FORMAT(p2.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
		 and p2.Branch = p.Branch		  and p2.IsCancel = 0 		
			),0) as PaySmartPurse

		, COALESCE( ( SELECT sum(d.Amount) - pos.payAmt
			  FROM tb_creditsale t
			  JOIN tb_creditsale_detail d on d.Credit_ID=t.id  
			  join tb_pos p1 on p1.ID = t.PosID
			  Join (SELECT p2.Branch
                    , sum(pp.PaymentAmount) payAmt 
                    ,  DATE_FORMAT(p2.SaleDate,'%Y') saledate
						FROM tb_pos p2 
        			  join tb_pos_payment pp on pp.Pos_id=p2.id  and p2.RemainAmount <> 0   and p2.IsCancel = 0 	
    			      group by p2.Branch,  DATE_FORMAT(p2.SaleDate,'%Y')
        			  ) pos on pos.Branch = p1.Branch and pos.SaleDate =DATE_FORMAT(p1.SaleDate,'%Y')
			  where t.IsDeleted = 0 and  DATE_FORMAT(p1.SaleDate,'%Y') = DATE_FORMAT(p.SaleDate,'%Y')
			   and p.Branch = p1.Branch	   and p1.IsCancel = 0 		
			  ),0) as Credit

			, COALESCE((SELECT  sum(t.RemainAmt ) 
			  FROM tb_creditsale t
			  Join tb_pos p2 on p2.id=t.PosID
			  where t.IsDeleted = 0 and  DATE_FORMAT(p2.SaleDate,'%Y') =DATE_FORMAT(p.SaleDate,'%Y')
			  and p2.Branch = p.Branch		
			 ),0) as Remain	  
			 
			, COALESCE((   
				SELECT SUM(s.Amount) 
 				From tb_smartpurse s 
				Where IsDeleted=0 and  DATE_FORMAT(s.CreatedDate,'%Y') Between ".$yfrom." and  ".$yto ."
				 And  p.Branch=s.BranchID
				Group by s.BranchID, DATE_FORMAT(s.CreatedDate,'%Y/%m') 
			) ,0) as  smartpurse 
		  
			, SaleDate as _date
			
		  From tb_pos p 

		   where 1=1    and p.IsCancel = 0 	 And  DATE_FORMAT(p.SaleDate,'%Y')  Between ".$yfrom." and  ".$yto; 

		if (isset($bid) && ($bid)!=0){
			$sql.=" and p.Branch =".$bid;
			$b="and p.Branch =".$bid;
		}
		$sql.= " group by DATE_FORMAT(SaleDate,'%Y')";
		// $sql.= " Union ";
		
		// $sql.=" SELECT DATE_FORMAT(s.CreatedDate,'%Y')  CreatedDate                                        
			// ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0                                                                                            
			// ,  (s.Amount) smartpurse , CreatedDate as _date                                                               
			// From tb_smartpurse s   
			// Where 1=1 and s.IsDeleted = 0                                                                                      
			// AND DATE_FORMAT(s.CreatedDate,'%Y') BETWEEN ".$yfrom." and  ".$yto."       
					// And NOT EXISTS (SELECT DATE_FORMAT(p.SaleDate,'%Y/%m/%d')                                       
						// FROM tb_pos p                                                                 
						// Where 1=1                                                                     
						// AND DATE_FORMAT(p.SaleDate,'%Y') BETWEEN ".$yfrom." and  ".$yto."
						// AND DATE_FORMAT(s.CreatedDate,'%Y') = DATE_FORMAT(p.SaleDate,'%Y')
					// )                                                                                
			// Group by DATE_FORMAT(s.CreatedDate,'%Y') ";
		
		$sql.=" order by _date ";
		
		$result = $this->ExecuteData($sql);
		
		return $result;
	}
	//******************************
	// Return Report
	//******************************
	function ReturnHeader(){
		return array('ลำดับที่','วันที่ส่งคืน','ชื่อสินค้า','ประเภทการส่งคืน','จำนวน');
	}	
	function ReturnReport($datefrom,$dateto, $branch=0, $type=0){
	
		$sql="select b.BranchName, DATE_FORMAT(rp.ReturnDate ,'%d/%m/%Y') as _ReturnDate , rp.Amount , rd.Amounts_r , p.ProductName,  rt.Type "
			." from tb_return_product rp"
			." join tb_return_product_detail rd on rp.ID=rd.ReturnID "
			." join tb_product p on p.ID=rd.Product "
			." join tb_branch b on b.ID=rp.Branch "
			." left join tb_return_type rt on rt.ID= rd.ReturnType_r "
			." Where 1=1 and rp.IsDeleted=0 and rp.Status=3 and DATE_FORMAT( rp.ReturnDate,'%Y/%m/%d' ) between '".$datefrom."' and '".$dateto. "'" ;
			
		if (isset($branch) && ($branch)!=0){
			$sql  .= " and rp.Branch=".$branch ;
		}
		if (isset($type) && ($type) !=0){
			$sql  .= " and rd.ReturnType_r=".$type;
		}
		$sql .= " order by  b.BranchName, rp.ReturnDate ";
		//	print $sql;
		$result = $this->ExecuteData($sql);
		$resultData = array();
		
		while($row = $result->FetchRow()){
			array_push($resultData,$row);
		}
		
		return $resultData;
	}


	//******************************
	// Order Branch Report
	//******************************
	function BranchOrderHeader(){
		return array('ลำดับที่','ชื่อสินค้า','จำนวนที่สั่ง','จำนวนที่รับจริง');
		
	}	
	
	function BranchOrderReport($datefrom,$dateto, $branch=0){
		
		//Sql Header
		$sql = "SELECT o.ID, o.OrderNo, b.BranchName, DATE_FORMAT(o.OrderDate ,'%d/%m/%Y') _OrderDate
			FROM tb_order_product o
			Left JOIN tb_branch b on o.Branch=b.ID
			Where o.Status=4 and DATE_FORMAT( OrderDate  , '%Y/%m/%d' ) Between '".$datefrom."' and '".$dateto."'";
	
		if (isset($branch) && ($branch)!=0){
			$sql  .= " and o.Branch=".$branch ;
		}
				
		$sql .= " order by OrderDate , b.BranchName ";
		$resultHeader = $this->ExecuteData($sql);
		// print $sql;
		//Sql Detai;
		 $sqlDetail="SELECT p.ProductName,   Amount_s ,  Amount_c
					FROM tb_order_product_detail r
					join tb_product p on r.Product = p.ID
					Where 1=1 ";
		//r.Receive = 10
		$result=array();
		//$header=array();
		$Detail=array();
		while($row = $resultHeader->FetchRow()){
			$sqlCondition =" and r.OrderID=".$row["ID"];
			$sqlOrder =" order by ProductName asc ";
			
			$sql =  $sqlDetail.$sqlCondition.$sqlOrder;
			
			
			$resultDetail = $this->ExecuteData($sql);
			//print $sql;
			while($rowDetail = $resultDetail->FetchRow()){
				array_push($Detail, $rowDetail);
			}
			$row["Detail"]=$Detail;
			array_push($result, $row);
			unset($Detail); 
			$Detail=array();
		}
		return $result;
	}
	//******************************
	// Order HO Report
	//******************************
	function OrderHeader(){
		return array('ลำดับที่','ชื่อสินค้า','จำนวนที่สั่ง','จำนวนที่รับจริง','ราคาต่อหน่วย' ,'รวม');
	}	
	function OrderReport($datefrom,$dateto, $supplierid=0){
		
		//Sql Header
		$sql = "SELECT r.ID, r.Code,s.SupplierCode, s.SupplierName , DATE_FORMAT(r.ReceivedDate ,'%d/%m/%Y')  as _ReceiveDate
				From tb_receive_product r
				LEFT Join tb_supplier s on r.Supplier=s.ID
				Where r.Status=1
				and DATE_FORMAT(ReceivedDate, '%Y/%m/%d' ) Between '".$datefrom."' and '".$dateto."'";
				
		if (isset($supplierid) && ($supplierid)!=0){
			$sql  .= " and r.Supplier=".$supplierid ;
		}
				
		$sql .= " order by ReceivedDate asc ";
		$resultHeader = $this->ExecuteData($sql);
		
		//Sql Detai;
		 $sqlDetail="SELECT p.ProductName, Amount_rq , Amount_rc ,  r.Price , Amount_rc* r.Price as Total
		 FROM tb_receive_product_detail r
		 join tb_product p on r.Product=p.ID
		 Where 1=1 ";
		 
		$result=array();
		//$header=array();
		$Detail=array();
		while($row = $resultHeader->FetchRow()){
			$sqlCondition =" and r.Receive=".$row["ID"];
			$sqlOrder =" order by ProductName asc ";
			
			$sql =  $sqlDetail.$sqlCondition.$sqlOrder;
			
			$resultDetail = $this->ExecuteData($sql);
			// print $sql;
			while($rowDetail = $resultDetail->FetchRow()){
				array_push($Detail, $rowDetail);
			}
			$row["Detail"]=$Detail;
			array_push($result, $row);
			unset($Detail); 
			$Detail=array();
		}
		return  $result;
	}
	
}
?>